package com.ywjh.inter.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.ywjh.inter.entity.InstitutionEntity;

public class InstitutionDAO {

	public int insert(Connection conn, InstitutionEntity entity) throws SQLException {
		int id = 0;
		PreparedStatement ps = conn.prepareStatement(
				"insert into institution(name,institution_type_id,hospital_type_id,nature_type_id,level_type_id,province_id,city_id,local_id,local_info,phone,system_name,op_status,department_type_id,units_name,units_level_1_id,units_level_2_id,create_time,create_op_id,is_root,p_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
				Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, entity.getName());
		ps.setInt(2, entity.getInstitutionTypeId());
		ps.setInt(3, entity.getHospitalTypeId());
		ps.setInt(4, entity.getNatureTypeId());
		ps.setInt(5, entity.getLevelTypeId());
		ps.setInt(6, entity.getProvinceId());
		ps.setInt(7, entity.getCityId());
		ps.setInt(8, entity.getLocalId());
		ps.setString(9, entity.getLocalInfo());
		ps.setString(10, entity.getPhone());
		ps.setString(11, entity.getSystemName());
		ps.setInt(12, entity.getOpStatus());
		ps.setInt(13, entity.getDepartmentTypeId());
		ps.setString(14, entity.getUnitsName());
		ps.setInt(15, entity.getUnitsLevel1Id());
		ps.setInt(16, entity.getUnitsLevel2Id());
		ps.setTimestamp(17, entity.getCreateTime());
		ps.setInt(18, entity.getCreateOpId());
		ps.setInt(19, entity.getIsRoot());
		ps.setInt(20, entity.getPid());
		if (ps.executeUpdate() > 0) {
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			rs.close();
		}
		ps.close();
		return id;
	}

	public List<InstitutionEntity> findByPid(Connection conn, int pid) throws SQLException {
		List<InstitutionEntity> list=new LinkedList<InstitutionEntity>();
		PreparedStatement ps=conn.prepareStatement("select * from institution where p_id=? order by id");
		ps.setInt(1, pid);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			InstitutionEntity entity=fill(rs);
			list.add(entity);
		}
		rs.close();
		return list;
	}

	private InstitutionEntity fill(ResultSet rs) throws SQLException {
		InstitutionEntity e=new InstitutionEntity();
		e.setId(rs.getInt("id"));
		e.setName(rs.getString("name"));
		e.setInstitutionTypeId(rs.getInt("institution_type_id"));
		e.setHospitalTypeId(rs.getInt("hospital_type_id"));
		e.setNatureTypeId(rs.getInt("nature_type_id"));
		e.setLevelTypeId(rs.getInt("level_type_id"));
		e.setProvinceId(rs.getInt("province_id"));
		e.setCityId(rs.getInt("city_id"));
		e.setLocalId(rs.getInt("local_id"));
		e.setLocalInfo(rs.getString("local_info"));
		e.setPhone(rs.getString("phone"));
		e.setSystemName(rs.getString("system_name"));
		e.setOpStatus(rs.getInt("op_status"));
		e.setDepartmentTypeId(rs.getInt("department_type_id"));
		e.setUnitsName(rs.getString("units_name"));
		e.setUnitsLevel1Id(rs.getInt("units_level_1_id"));
		e.setUnitsLevel2Id(rs.getInt("units_level_2_id"));
		e.setCreateTime(rs.getTimestamp("create_time"));
		e.setCreateOpId(rs.getInt("create_op_id"));
		e.setIsRoot(rs.getInt("is_root"));
		e.setPid(rs.getInt("p_id"));
		return e;
	}

	public InstitutionEntity findById(Connection conn,int id) throws SQLException {
		InstitutionEntity entity=null;
		PreparedStatement ps=conn.prepareStatement("select * from institution where id = ?");
		ps.setInt(1, id);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			entity=fill(rs);
		}
		rs.close();
		return entity;
	}

	public boolean update(Connection conn, InstitutionEntity entity) throws SQLException {
		
		boolean b=false;
		
		PreparedStatement ps=conn.prepareStatement("update institution set name=?,institution_type_id=?,hospital_type_id=?,nature_type_id=?,level_type_id=?,province_id=?,city_id=?,local_id=?,local_info=?,phone=?,system_name=?,op_status=?,department_type_id=?,units_name=?,units_level_1_id=?,units_level_2_id=?,create_time=?,create_op_id=?,is_root=?,p_id=? where id = ?");
		ps.setString(1, entity.getName());
		ps.setInt(2, entity.getInstitutionTypeId());
		ps.setInt(3, entity.getHospitalTypeId());
		ps.setInt(4, entity.getNatureTypeId());
		ps.setInt(5, entity.getLevelTypeId());
		ps.setInt(6, entity.getProvinceId());
		ps.setInt(7, entity.getCityId());
		ps.setInt(8, entity.getLocalId());
		ps.setString(9, entity.getLocalInfo());
		ps.setString(10, entity.getPhone());
		ps.setString(11, entity.getSystemName());
		ps.setInt(12, entity.getOpStatus());
		ps.setInt(13, entity.getDepartmentTypeId());
		ps.setString(14, entity.getUnitsName());
		ps.setInt(15, entity.getUnitsLevel1Id());
		ps.setInt(16, entity.getUnitsLevel2Id());
		ps.setTimestamp(17, entity.getCreateTime());
		ps.setInt(18, entity.getCreateOpId());
		ps.setInt(19, entity.getIsRoot());
		ps.setInt(20, entity.getPid());
		ps.setInt(21, entity.getId());
		if(ps.execute()) {
			b=true;
		}
		return b;
	}
	
	public List<InstitutionEntity> findRootList(Connection conn) throws SQLException {
		List<InstitutionEntity> list = new LinkedList<>();
		PreparedStatement ps = conn.prepareStatement("select * from institution where is_root=1 and op_status=1");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			InstitutionEntity entity = fill(rs);
			list.add(entity);
		}
		return list;
	}

	public List<InstitutionEntity> findBedByPid(Connection conn, int inst_id) throws SQLException {
		List<InstitutionEntity> list=new LinkedList<>();
		PreparedStatement ps=conn.prepareStatement("select * from institution where op_status=1 and institution_type_id=4 and p_id=? order by id");
		ps.setInt(1, inst_id);
		ResultSet rs=ps.executeQuery();
		while(rs.next()) {
			InstitutionEntity e=fill(rs);
			list.add(e);
		}
		rs.close();
		return list;
	}

}
